package cn.tedu.dao;

import cn.tedu.entity.Product;
import cn.tedu.utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ProductDao {
    public void insert(Product product) {
        try(Connection conn = DBUtils.getConn()){
            String sql = "insert into product values(null,?,?,?,?,?,0,0)";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,product.getTitle());
            ps.setString(2,product.getAuthor());
            ps.setString(3,product.getIntro());
            ps.setInt(4,product.getCategoryId());
            ps.setString(5,product.getImgPath());
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public List<Product> findAll() {
        ArrayList<Product> list = new ArrayList<>();
        try(Connection conn = DBUtils.getConn()){
            String sql = "select * from product limit 0,5";
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery(sql);
            while (rs.next()){
                int id = rs.getInt(1);
                String title = rs.getString(2);
                String author = rs.getString(3);
                String intro = rs.getString(4);
                int categoryId = rs.getInt(5);
                String imgPath = rs.getString(6);
                int viewCount = rs.getInt(7);
                int likeCount = rs.getInt(8);
                list.add(new Product(id,title,author,intro,categoryId,imgPath,viewCount,likeCount));
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }

    public List<Product> findByCId(String cid) {
        ArrayList<Product> list = new ArrayList<>();
        try(Connection conn = DBUtils.getConn()){
            String sql = "select * from product where categoryId=? limit 0,5";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(cid));
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                int id = rs.getInt(1);
                String title = rs.getString(2);
                String author = rs.getString(3);
                String intro = rs.getString(4);
                int categoryId = rs.getInt(5);
                String imgPath = rs.getString(6);
                int viewCount = rs.getInt(7);
                int likeCount = rs.getInt(8);
                list.add(new Product(id,title,author,intro,categoryId,imgPath,viewCount,likeCount));
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }

    public List<Product> findByKeyword(String keyword) {
        ArrayList<Product> list = new ArrayList<>();
        try(Connection conn = DBUtils.getConn()){
            String sql = "select * from product where title like ? or intro like ? or author like ? limit 0,5";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,"%"+keyword+"%");
            ps.setString(2,"%"+keyword+"%");
            ps.setString(3,"%"+keyword+"%");
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                int id = rs.getInt(1);
                String title = rs.getString(2);
                String author = rs.getString(3);
                String intro = rs.getString(4);
                int categoryId = rs.getInt(5);
                String imgPath = rs.getString(6);
                int viewCount = rs.getInt(7);
                int likeCount = rs.getInt(8);
                list.add(new Product(id,title,author,intro,categoryId,imgPath,viewCount,likeCount));
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }

    public List<Product> findViewList() {
        ArrayList<Product> list = new ArrayList<>();
        try(Connection conn = DBUtils.getConn()){
            String sql = "select * from product order by viewCount desc limit 0,4";
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery(sql);
            while (rs.next()){
                int id = rs.getInt(1);
                String title = rs.getString(2);
                String author = rs.getString(3);
                String intro = rs.getString(4);
                int categoryId = rs.getInt(5);
                String imgPath = rs.getString(6);
                int viewCount = rs.getInt(7);
                int likeCount = rs.getInt(8);
                list.add(new Product(id,title,author,intro,categoryId,imgPath,viewCount,likeCount));
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }

    public List<Product> findLikeList() {
        ArrayList<Product> list = new ArrayList<>();
        try(Connection conn = DBUtils.getConn()){
            String sql = "select * from product order by likeCount desc limit 0,4";
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery(sql);
            while (rs.next()){
                int id = rs.getInt(1);
                String title = rs.getString(2);
                String author = rs.getString(3);
                String intro = rs.getString(4);
                int categoryId = rs.getInt(5);
                String imgPath = rs.getString(6);
                int viewCount = rs.getInt(7);
                int likeCount = rs.getInt(8);
                list.add(new Product(id,title,author,intro,categoryId,imgPath,viewCount,likeCount));
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }

    public void addviewCount(String id) {
        try(Connection conn = DBUtils.getConn()){
            String sql = "update product set viewCount=viewCount+1 where id=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(id));
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public Product findById(String id) {
        try(Connection conn = DBUtils.getConn()){
            String sql = "select * from product where id=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(id));
            ResultSet rs = ps.executeQuery();
            if (rs.next()){
                String title = rs.getString(2);
                String author = rs.getString(3);
                String intro = rs.getString(4);
                int categoryId = rs.getInt(5);
                String imgPath = rs.getString(6);
                int viewCount = rs.getInt(7);
                int likeCount = rs.getInt(8);
                return new Product(Integer.parseInt(id),title,author,intro,categoryId,imgPath,viewCount,likeCount);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    public void addlikeCount(String id) {
        try(Connection conn = DBUtils.getConn()){
            String sql = "update product set likeCount=likeCount+1 where id=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(id));
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public void deleteByID(String id) {
        try(Connection conn = DBUtils.getConn()){
            String sql = "delete from product where id=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(id));
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public List<Product> loadMore(String count) {
        ArrayList<Product> list = new ArrayList<>();
        try(Connection conn = DBUtils.getConn()){
            String sql = "select * from product limit ?,?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(count));
            ps.setInt(2,5);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                int id = rs.getInt(1);
                String title = rs.getString(2);
                String author = rs.getString(3);
                String intro = rs.getString(4);
                int categoryId = rs.getInt(5);
                String imgPath = rs.getString(6);
                int viewCount = rs.getInt(7);
                int likeCount = rs.getInt(8);
                list.add(new Product(id, title, author, intro, categoryId, imgPath, viewCount, likeCount));
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }
}
